import json
import random
from datetime import datetime,timedelta
from collections import OrderedDict
from PIL.ImImagePlugin import number
from rest_framework.views import APIView
from rest_framework.response import Response
from django.db import connection
from twisted.conch.insults.window import cursor

from dvadmin.utils.cursor_util import get_dict_from_cursor
from .. import models

YEAR_MAX = 99999
YEAR_MIN = -1


class HistoryDataView(APIView):
    """
    获取爬取到的所有历史数据信息
    """

    def get(self, request):

        # 获取所有爬取过的期刊
        cursor = connection.cursor()
        cursor.execute("SELECT DISTINCT agent_id FROM agent_record")
        journal_count = len(cursor.fetchall())

        # 获取所有爬取过的年份
        cursor.execute("SELECT DISTINCT year FROM agent_record where last_query_num != -1")
        years = [int(row[0]) for row in cursor.fetchall()]
        max_year, min_year = YEAR_MIN, YEAR_MAX
        doc_count = 0
        for year in years:
            year = int(year)
            max_year = max(max_year, year)
            min_year = min(min_year, year)

            # 获取该年份的文档数量
            cursor.execute(f"SELECT COUNT(1) FROM document_{year}")
            result = cursor.fetchone()
            if result is None:
                continue
            doc_count += result[0]

        connection.commit()
        cursor.close()
        return Response(
            data={
                "code": 2000,
                "msg": "获取成功",
                "data": {
                    'journalCount': journal_count,
                    'docCount': doc_count,
                    'years': years,
                    'maxYear': None if max_year == YEAR_MIN else max_year,
                    'minYear': None if min_year == YEAR_MAX else min_year
                }
            },
            status=200)


class AgentYearView(APIView):
    """
    获取指定期刊智能体的年份范围信息
    """

    def get(self, request):
        # 获取请求参数中的id参数
        params = request.query_params
        agent_id = params.get('id')

        # 获取该智能体的所有爬取年份
        cursor = connection.cursor()
        max_year, min_year = YEAR_MIN, YEAR_MAX
        cursor.execute(f"SELECT year FROM agent_record WHERE agent_id={agent_id}")
        years = [int(row[0]) for row in cursor.fetchall()]
        for year in years:
            max_year = max(max_year, year)
            min_year = min(min_year, year)
        connection.commit()
        cursor.close()

        return Response(
            data={
                "code": 2000,
                "msg": "获取成功",
                "data": {
                    'years': years,
                    'maxYear': None if max_year == YEAR_MIN else max_year,
                    'minYear': None if min_year == YEAR_MAX else min_year
                }
            },
            status=200)


class AgentRecordView(APIView):
    """
    获取智能体的爬取记录信息
    """

    def get(self, request):
        # 获取请求参数中的id参数
        params = request.query_params
        agent_id = params.get('id')

        # 获取爬取记录
        cursor = connection.cursor()
        cursor.execute(f"select * from agent_record where agent_id={agent_id}")
        result = get_dict_from_cursor(cursor)
        return Response(
            data={
                "code": 2000,
                "msg": "获取成功",
                "data": result
            },
            status=200)


class AgentInfoView(APIView):
    """
    获取所有智能体 或 模糊搜索匹配期刊名前缀的智能体
    """

    def get(self, request):
        params = request.query_params
        key = params.get('key')
        # 页码
        page_num = params.get('pageNum') or 1
        # 页大小
        page_size = params.get('pageSize') or 50
        # 是否按照status进行筛选
        status = params.get('status') or None
        # 是否按照is_running进行筛选
        is_running = params.get('is_running') or None

        # 分页sql
        page_sql = f'limit {(int(page_num) - 1) * int(page_size)}, {int(page_size)}'

        cursor = connection.cursor()
        select_sql = f"select * from agent"
        if key is None and status is None and is_running is None:
            select_sql += f" {page_sql}"
        else:
            flag = 0
            select_sql += f" WHERE"
            if status is not None:
                if flag == 1:
                    select_sql += " and"
                flag = 1
                select_sql += f" status={status}"

            if is_running is not None:
                if flag == 1:
                    select_sql += " and"
                flag = 1
                select_sql += f" is_running={is_running}"

            if key is not None:
                if flag == 1:
                    select_sql += " and"
                flag = 1
                select_sql += f" journal like '{key}%'"
            select_sql += f" {page_sql}"

        cursor.execute(select_sql)
        result = get_dict_from_cursor(cursor)
        return Response(
            data={
                "code": 2000,
                "msg": "获取成功",
                "data": result
            },
            status=200)


class AgentStatisticView(APIView):
    """
    获取指定智能体的 年份 实际、预期爬取文献数量 消耗的时间
    """

    def get(self, request):
        # 获取请求参数中的id参数
        params = request.query_params
        agent_id = params.get('id')

        # 获取智能体的统计信息
        cursor = connection.cursor()
        cursor.execute(f"select journal from agent where id={agent_id}")
        journal = cursor.fetchone()

        if journal is None:
            return Response(
                data={
                    "code": 400,
                    "msg": "智能体非法"
                },
                status=200)
        journal = journal[0]
        cursor.execute(f"select id,year,last_query_num from agent_record where agent_id={agent_id}")
        rows = cursor.fetchall()
        result = {}

        for record_id, year, expect_num in rows:
            real_num, cost_time = 0, 0
            # 获取真实数量
            cursor.execute(f"select count(1) from document_{year} where source_title='{journal}'")
            temp = cursor.fetchone()
            if temp:
                real_num = temp[0]

            # 获取爬取耗时
            cursor.execute(f"select cost_time from crawl_record where agent_record_id={record_id}")
            times = cursor.fetchall()
            for time in times:
                cost_time += time[0]

            # 添加到结果列表中
            result[year]={
                'realNum': real_num,
                'expectNum': expect_num,
                'costTime': cost_time
            }
        connection.commit()
        cursor.close()

        return Response(
            data={
                "code": 2000,
                "msg": "获取成功",
                "data": result
            },
            status=200)


class SpiderDocumentView(APIView):
    """
    获取指定期刊（智能体）在指定年份的文档数据
    """

    def get(self, request):
        # 获取请求参数中的id参数
        params = request.query_params
        agent_id = params.get('id')
        year = params.get('year')
        page_num = params.get('pageNum') or 1
        page_size = params.get('pageSize') or 10

        # 根据ID获取到指定期刊名
        cursor = connection.cursor()
        cursor.execute(f"select journal from agent where id={agent_id}")
        journal_name = cursor.fetchone()
        if journal_name is None:
            return Response(data={"code": 400, "msg": "无效的ID"}, status=200)
        journal_name = journal_name[0]

        # 获取指定期刊在指定年份的文档数据
        page_sql = f'limit {(int(page_num) - 1) * int(page_size)}, {int(page_size)}'
        cursor.execute(f"select * from document_{year} where source_title='{journal_name}' {page_sql}")

        # 将返回结果转换为字典
        result = get_dict_from_cursor(cursor)
        connection.commit()
        cursor.close()

        return Response(data={"code": 2000, "msg": "获取成功", "data": result}, status=200)


class AgentLogView(APIView):
    """
    获取智能体的日志信息
    """

    def get(self, request):
        # 获取请求参数中的id参数
        params = request.query_params
        agent_id = params.get('id')
        year = params.get('year')
        page_num = params.get('pageNum') or 1
        page_size = params.get('pageSize') or 10

        cursor = connection.cursor()
        # 分页sql
        if page_num and page_size:
            page_sql = f'limit {(int(page_num) - 1) * int(page_size)}, {int(page_size)}'
        else:
            page_sql = ''

        # 查询日志信息
        agent_record_sql = f"select id from agent_record where agent_id={agent_id} and year={year}"
        cursor.execute("select * from "
                       f"({agent_record_sql}) ar left join crawl_log cl"
                       f"on cl.agent_record_id=ar.id {page_sql}")

        result = get_dict_from_cursor(cursor)
        connection.commit()
        cursor.close()

        return Response(data={"code": 2000, "msg": "获取成功", "data": result}, status=200)

class AllLogView(APIView):
    def get(self,request):
        log_sql = "SELECT * FROM crawl_log ORDER BY id DESC"
        with connection.cursor() as cursor:
            cursor.execute(log_sql)
            columns = [col[0] for col in cursor.description]  # 获取列名
            results = cursor.fetchall()  # 获取所有结果

            # 将结果转换为字典格式
            logs = [dict(zip(columns, row)) for row in results]

        return Response(data={"code": 2000, "msg": "获取成功", "data": logs}, status=200)

class AgentCountView(APIView):
    def get(self,request):
        agent_count_sql = "SELECT COUNT(id) FROM agent"
        with connection.cursor() as cursor:
            cursor.execute(agent_count_sql)
            count = cursor.fetchone()  # 获取所有结果
        result = count[0] if count else 0

        return Response(data={"code": 2000, "msg": "获取成功", "data": {"agent_count":result}}, status=200)

class AgentTaskMonitorView(APIView):
    def get(self,request):
        params = request.query_params
        agent_id = params.get('id')

        with connection.cursor() as cursor:
            # 根据agent_id查询到agent的基本信息
            agent_info_sql = f"SELECT journal,status,is_running FROM agent WHERE id={agent_id}"
            cursor.execute(agent_info_sql)
            sql_data = cursor.fetchone()
            if sql_data is None:
                return Response(data={"code": 400, "msg": "无效的ID"}, status=200)
            journal = sql_data[0]
            status = sql_data[1]
            is_running = sql_data[2]
            #根据agent_id查询到agent_record记录
            agent_taskyear_sql = f"SELECT id,year FROM agent_record WHERE agent_id={agent_id}"
            cursor.execute(agent_taskyear_sql)
            sql_data = cursor.fetchall()
            result = {}
            crawl_create_time = {}
            taskyear_list = []
            # 一个智能体有多个爬取年份，对于每个year和record_id进行查询 爬取细节
            for row in sql_data:
                agent_record_id = row[0]
                taskyear = row[1]

                # 爬取时间
                agent_taskcreatetime_sql = f"SELECT create_time FROM crawl_record WHERE agent_record_id={agent_record_id}"
                cursor.execute(agent_taskcreatetime_sql)
                sql_data = cursor.fetchone()
                if sql_data is not None:
                    crawl_create_time[taskyear] = sql_data[0]
                    taskyear_list.append(taskyear)
                # 爬取日志
                crawllog_info_sql = f"SELECT status,info,create_time FROM crawl_log WHERE agent_record_id={agent_record_id}"
                cursor.execute(crawllog_info_sql)
                if cursor.rowcount > 0:
                    result[taskyear] = get_dict_from_cursor(cursor)




        return Response(data={"code": 2000,
                              "msg": "获取成功",
                              "data":
                                  {"journal": journal,
                                    "status": status,
                                    "is_running": is_running,
                                    "taskyear": taskyear_list,
                                    "crawl_create_time": crawl_create_time,
                                    "crawl_log": result}
                              },
                        status=200)

class CrawlSpeedProgress(APIView):
    def get(self,request):
        params = request.query_params
        agent_sum = int(params.get('agent_sum'))
        last_progress = float(params.get('last_progress'))

        # 将时间戳转换为毫秒
        timestamp = int(datetime.now().timestamp())

        with connection.cursor() as cursor:
            # 最近一分钟的爬取速度
            agent_record_sql = f"SELECT MAX(last_crawl_time),MIN(last_crawl_time),SUM(last_query_num) FROM agent_record WHERE last_crawl_time<={timestamp} AND last_crawl_time>={timestamp - timedelta(minutes=1).total_seconds()}"
            cursor.execute(agent_record_sql)
            sql_data = cursor.fetchone()
            # print("-------------------------")
            # print(timestamp)
            # print("-------------------------")
            if sql_data:
                max_time,min_time,sum_num = sql_data[0] or 1000,sql_data[1] or 0,sql_data[2] or 0
                speed = int((sum_num * 1000) / (max_time - min_time))
            else:
                speed = -1

            # 总任务爬取进度%
            agent_sql = f"SELECT SUM(is_running) FROM agent"
            cursor.execute(agent_sql)
            progress = float(format((agent_sum-cursor.fetchone()[0]) / agent_sum, ".2f"))
            if(progress<last_progress or (1<progress and progress<0)): progress = last_progress

        return Response(data={"code": 2000,
                              "msg": "获取成功",
                              "data": {"speed":speed,
                                       "progress_percentage":progress}
                              },
                        status=200)

class Random(APIView):
    def get(self,request):
        return Response(data={"code": 2000,
                              "msg": "获取成功",
                              "data": float(format(random.uniform(1.0, 10.0), ".2f"))
                              },
                        status=200)